<%
'######################################################################
'## dao.work.asp
'## -------------------------------------------------------------------
'## Feature     :   AspBox Mvc-Dao Work Class
'## Version     :   v1.0
'## Author      :   Lajox(lajox@19www.com)
'## Update Date :   2012/04/29 23:32
'## Description :   AspBox MVC模型之Dao层之数据库工作模块
'######################################################################

Class Cls_Dao_Work

	Private Sub Class_Initialize()

	End Sub

	Private Sub Class_Terminate()

	End Sub

	'------------------------------------------------------------------------------
	'# Dao.Work.CreatDB_MsAcc(DbPath, DbFileName, DbUpwd)
	'# @return: boolean
	'# @dowhat: 创建MsAccess数据库(已存在则覆盖)
	'# 			创建成功返回 True 失败 False
	'--DESC------------------------------------------------------------------------
	'# @param DbPath 		[string] : 目标目录信息
	'# @param DbFileName 	[string] : 目标库文件名称
	'# @param DbUpwd 		[string] : 目标库打开密码
	'--DEMO------------------------------------------------------------------------
	'# Dim isMaked : isMaked = False
	'# AB.Use "Mvc" : Dao.Use "Work" : isMaked = Dao.Work.CreatDB_MsAcc("E:\", "test.mdb", "")
	'# If isMaked Then AB.C.Print "成功创建Access数据库" Else AB.C.Print "创建失败"
	'#-----------------------------------------------------------------------------

	Public Function CreatDB_MsAcc(ByVal DbPath,ByVal DbFileName,ByVal DbUpwd)
		CreatDB_MsAcc=False
		On Error GoTo 0
		On Error Resume Next
		DIM fxztxt,fu_fu_db_str,fu_db_str,fu_fso,fu_Ca,fu_Je,fu_Conn,fu_Sql_Str,fu_Rs
		fxztxt=Chr(60)&"%Response.end()%"&Chr(62)
		If Right(DbPath,1)<>"\" Then DbPath=DbPath & "\"
		fu_fu_db_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&DbPath&"temp.mdb;"
		fu_db_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&DbPath&DbFileName&";Jet OLEDB:Database Password="&DbUpwd&";"
		Set fu_fso = CreateObject(AB.fsoName)
		If fu_fso.FileExists(DbPath&DbFileName) Then fu_fso.DeleteFile(DbPath&DbFileName)
		Set fu_fso = Nothing
		Set fu_Ca = Server.CreateObject("ADOX.Catalog")
		fu_Ca.Create fu_fu_db_str
		Set fu_Ca = Nothing
		Set fu_Je = Server.CreateObject("JRO.JetEngine")
		fu_Je.CompactDatabase fu_fu_db_str,fu_db_str
		Set fu_fso = CreateObject(AB.fsoName)
		fu_fso.DeleteFile(DbPath&"temp.mdb")
		Set fu_Je   = Nothing
		Set fu_fso  = Nothing
		set fu_Conn = Server.CreateObject("ADODB.Connection")
		set fu_Rs   = Server.CreateObject("ADODB.Recordset")
		fu_Conn.open fu_db_str
		fu_Sql_Str = "CREATE TABLE [0] ([0] Text DEFAULT Notxt NOT NULL,[11] int IDENTITY (1, 1) NOT NULL PRIMARY KEY)"
		fu_Conn.Execute(fu_Sql_Str)
		fu_Sql_Str = "Select * From [0]"
		fu_Rs.open fu_Sql_Str,fu_Conn,1,3
		fu_Rs.addnew
		fu_Rs("0")=fxztxt
		fu_Rs.update
		fu_Rs.Close
		fu_Conn.Close
		Set fu_Rs = Nothing
		Set fu_Conn = Nothing
		If Err.Number = 0 Then CreatDB_MsAcc = True
		On Error GoTo 0
	End function

	'------------------------------------------------------------------------------
	'# Dao.Work.CreatDB_MsSQL(DbIp, DbSamc, DbSapwd, DbName, DbUpmc, DbUpwd)
	'# @return: boolean
	'# @dowhat: 创建MsSQLServer数据库
	'# 			创建成功返回 True 失败 False
	'--DESC------------------------------------------------------------------------
	'# @param DbIp 			[string] : 数据库所在IP或主机名称
	'# @param DbSamc 		[string] : 数据库超管用户名称
	'# @param DbSapwd 		[string] : 数据库超管用户口令
	'# @param DbName 		[string] : 新建数据库名称
	'# @param DbSapwd 		[string] : 新建数据库所属用户名称
	'# @param DbUpmc 		[string] : 新建数据库所属用户密码
	'--DEMO------------------------------------------------------------------------
	'#
	'#-----------------------------------------------------------------------------

	Public Function CreatDB_MsSQL(ByVal DbIp,ByVal DbSamc,ByVal DbSapwd,ByVal DbName,ByVal DbUpmc,ByVal DbUpwd)
		CreatDB_MsSQL=False
		On Error GoTo 0
		On Error Resume Next
		DIM fu_Sa_Str,fu_Ua_Str,fu_Conn,fu_Rs,fu_Sql_Str,fxztxt,fu_DB_Conn_Str
		fxztxt=Chr(60)&"%Response.end()%"&Chr(62)
		fu_Sa_Str  ="DRIVER=SQL Server;UID="&DbSamc&";DATABASE=master;SERVER="&DbIp&";PWD="&DbSapwd&";"
		fu_Ua_Str  ="DRIVER=SQL Server;UID="&DbUpmc&";DATABASE="&DbName&";SERVER="&DbIp&";PWD="&DbUpwd&";"
		Set fu_Conn = Server.CreateObject("ADODB.Connection")
		fu_Conn.Open fu_Sa_Str
		fu_Conn.Execute "CREATE DATABASE " &DbName
		fu_Conn.Close
		fu_DB_Conn_Str="DRIVER=SQL Server;UID="&DbSamc&";DATABASE="&DbName&";SERVER="&DbIp&";PWD="&DbSapwd&";"
		fu_Conn.Open fu_DB_Conn_Str
		fu_Sql_Str="EXEC sp_addlogin '"&DbUpmc&"','"&DbUpwd&"','"&DbName&"'"
		fu_Conn.Execute fu_Sql_Str
		fu_Sql_Str="EXEC sp_grantdbaccess '"&DbUpmc&"'"
		fu_Conn.Execute fu_Sql_Str
		fu_Sql_Str="EXEC sp_addrolemember 'db_owner', '"&DbUpmc&"'"
		fu_Conn.Execute fu_Sql_Str
		fu_Sql_Str="EXEC sp_defaultdb "&DbUpmc&","&DbName
		fu_Conn.Execute fu_Sql_Str
		fu_Conn.Close
		fu_Conn.open fu_Ua_Str
		fu_Sql_Str="CREATE TABLE [0] ([0] Text DEFAULT ('Notxt') NOT NULL,[11] int IDENTITY (1, 1) NOT NULL PRIMARY KEY)"
		fu_Conn.Execute fu_Sql_Str
		Set fu_Rs=server.createobject("ADODB.Recordset")
		fu_Sql_Str="Select * From [0]"
		fu_Rs.open fu_Sql_Str,fu_Conn,1,3
		fu_Rs.addnew
		fu_Rs("0")=fxztxt
		fu_Rs.update
		fu_Rs.Close
		fu_Conn.Close
		Set fu_Rs = Nothing
		Set fu_Conn=Nothing
		If Err.Number = 0 Then CreatDB_MsSQL = True
		On Error GoTo 0
	End function

	'------------------------------------------------------------------------------
	'# Dao.Work.CreatTable(ConnObj, Tabnamestr, CvArrstr, SqlType)
	'# @return: boolean
	'# @dowhat: 创建数据表
	'# 			创建成功返回 True 失败 False
	'--DESC------------------------------------------------------------------------
	'# @param ConnObj 		[object] : 数据库链接对象
	'# @param Tabnamestr 	[string] : 数据表名称
	'# @param CvArrstr 		[string] : 字段表 (写法： Fname1#Type#Len#Defvalue|Fname1#Type#Len#Defvalue|...) 最后一个不要写“|”
	'# @param SqlType 		[string] : Sql语句类型 (0 Access 1 Mssqlserver)
	'# ===Fname,Type,Len,Defvalue 说明：字段名称,字段类型,字段长度,默认值
	'# ===字段类型 Type：
	'#      C/c 字符 T/t 文本 I/i 二进制 D/d 日期 M/m
	'#      关键字(字符型) A/a 关键字自动编号(数值型) N/n 数值(float) Z/z 数值(int)
	'--DEMO------------------------------------------------------------------------
	'# Dim isMaked : isMaked = False
	'# AB.Use "Mvc" : Dao.Use "Work" : isMaked = Dao.Work.CreatTable(AB.db.Conn, "table01", "fi#a##|fa#t##|fb#c#20#a|fc#n##5", 0)
	'# If isMaked Then AB.C.Print "数据表创建成功" Else AB.C.Print "数据表创建失败"
	'#-----------------------------------------------------------------------------

	Public Function CreatTable(ByVal ConnObj,ByVal Tabnamestr,ByVal CvArrstr,ByVal SqlType)
		CreatTable=False
		On Error GoTo 0
		On Error Resume Next
		Dim filsarry,NeFilarry,Filstr,spfstr,templx,def_kh_l,def_kh_r,TempSqlStr,ai,fu_Conn
		CvArrstr=Trim(CvArrstr) : If Right(CvArrstr,1)="|" Then CvArrstr=Left(CvArrstr,Len(CvArrstr)-1)
		def_kh_l="":def_kh_r="":Filstr="":spfstr="":TempSqlStr=""
		filsarry=Split(CvArrstr,"|")
		For ai = LBound(filsarry) To UBound(filsarry)
			NeFilarry=Split(filsarry(ai),"#")
			templx=""
			If UCase(NeFilarry(1))="C" Then templx="varchar(" & NeFilarry(2) & ")"
			If UCase(NeFilarry(1))="T" Then templx="TEXT"
			If UCase(NeFilarry(1))="I" Then templx="image"
			If UCase(NeFilarry(1))="D" Then templx="datetime"
			If UCase(NeFilarry(1))="M" Then templx="varchar(" & NeFilarry(2) & ") NOT NULL PRIMARY KEY"
			If UCase(NeFilarry(1))="A" Then templx="Int IDENTITY (1,1) NOT NULL PRIMARY KEY"
			If UCase(NeFilarry(1))="N" Then templx="Float"
			If UCase(NeFilarry(1))="Z" Then templx="Int"
			If SqlType =1 Then
				def_kh_l="('"
				def_kh_r="')"
			End If
			If Trim(NeFilarry(3))<>"" Then templx=templx &" DEFAULT " & def_kh_l & Trim(NeFilarry(3)) & def_kh_r
			If ai<>UBound(filsarry) Then
				spfstr= spfstr & "[" & NeFilarry(0) & "] " & templx &","
			Else
				spfstr= spfstr & "[" & NeFilarry(0) & "] " & templx
			End If
		Next
		TempSqlStr="CREATE TABLE ["&Trim(Tabnamestr)&"] (" & spfstr & ")"
		set fu_Conn=ConnObj
		fu_Conn.Execute TempSqlStr
		fu_Conn.Close
		Set fu_Conn=Nothing
		If Err.Number = 0 Then CreatTable = True
		On Error GoTo 0
	End Function

	'------------------------------------------------------------------------------
	'# Dao.Work.InterTBValue(ConnObj, Tabnamestr, CvArrstr)
	'# @return: boolean
	'# @dowhat: 在数据库中插入字段值
	'# 			插入成功返回 True 失败 False
	'--DESC------------------------------------------------------------------------
	'# @param ConnObj 		[object] : 数据库链接对象
	'# @param Tabnamestr 	[string] : 数据表名称
	'# @param CvArrstr 		[string] : 字段表 (写法： Fname1#Value|Fname2#Value|...) 最后一个不要写“|”
	'# ===Fname,Value 说明：字段名称,字段值
	'--DEMO------------------------------------------------------------------------
	'# Dim isMaked : isMaked = False
	'# AB.Use "Mvc" : Dao.Use "Work" : isMaked = Dao.Work.InterTBValue(AB.db.Conn, "table02", "fa#x|fb#y|fc#z")
	'# If isMaked Then AB.C.Print "表插入数据成功" Else AB.C.Print "表插入数据失败"
	'#-----------------------------------------------------------------------------

	Public Function InterTBValue(ByVal ConnObj,ByVal Tabnamestr,ByVal CvArrstr)
		InterTBValue=False
		On Error GoTo 0
		On Error Resume Next
		Dim def_kh_l,def_kh_r,Filarray,Valuearray,Temparraya,Temparrayb,TempSqlStr1,fai,fu1_Conn
		def_kh_l  =""
		def_kh_r  =""
		Temparraya=Split(CvArrstr,"|")
		For fai = LBound(Temparraya) To UBound(Temparraya)
			Temparrayb=Split(Temparraya(fai),"#")
			If (fai<> UBound(Temparraya)) Then
				Filarray  =Filarray & "[" & Temparrayb(0) & "],"
				Valuearray=Valuearray & "'" & Temparrayb(1) & "',"
			Else
				Filarray  =Filarray & "[" & Temparrayb(0) & "]"
				Valuearray=Valuearray & "'" & Temparrayb(1) & "'"
			End If
		Next
		TempSqlStr1="INSERT INTO [" & Tabnamestr & "] (" & Filarray & ") VALUES (" & Valuearray & ")"
		set fu1_Conn=ConnObj
		fu1_Conn.Execute TempSqlStr1
		fu1_Conn.Close
		Set fu1_Conn=Nothing
		If Err.Number = 0 Then InterTbValue=True
		On Error GoTo 0
	End Function

	'------------------------------------------------------------------------------
	'# Dao.Work.dbExists(ConnObj)
	'# @return: boolean
	'# @dowhat: 检测数据库(数据库链接对象)是否存在
	'--DESC------------------------------------------------------------------------
	'# @param ConnObj [object] : 数据库链接对象
	'--DEMO------------------------------------------------------------------------
	'# Dim isMaked : isMaked = False
	'# AB.Use "Mvc" : Dao.Use "Work" : isMaked = Dao.Work.dbExists(AB.db.Conn)
	'# AB.C.Print isMaked
	'#-----------------------------------------------------------------------------

	Public Function dbExists(ByVal ConnObj)
		dbExists=False
		Dim t_Conn
		On Error GoTo 0
		On Error Resume Next
		If IsObject(ConnObj) And Lcase(TypeName(ConnObj))="connection" Then Set t_Conn = ConnObj
		If t_Conn.State = 0 Then t_Conn.Open
		If Err.Number=0 Then
			dbExists=True
		End If
		On Error GoTo 0
		Set t_Conn = Nothing
	End Function

	'------------------------------------------------------------------------------
	'# Dao.Work.tbExists(ConnObj)
	'# @return: boolean
	'# @dowhat: 检测数据表是否存在
	'--DESC------------------------------------------------------------------------
	'# @param ConnObj [object] : 数据库链接对象
	'--DEMO------------------------------------------------------------------------
	'# Dim isMaked : isMaked = False
	'# AB.Use "Mvc" : Dao.Use "Work" : isMaked = Dao.Work.tbExists(AB.db.Conn,"table01")
	'# AB.C.Print isMaked
	'#-----------------------------------------------------------------------------

	Public Function tbExists(ByVal ConnObj,ByVal TableName)
		tbExists=False
		DIM t_Rs : Set t_Rs=server.createobject("ADODB.Recordset")
		On Error GoTo 0
		On Error Resume Next
		t_Rs.Open "SELECT * FROM "&TableName,ConnObj,1,1
		t_Rs.Close
		If Err.Number = 0 Then tbExists=True
		Set t_Rs = Nothing
		On Error GoTo 0
	End Function

End Class
%>